import MySQLdb

# 连接数据库
def connect():
    db = MySQLdb.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='',
        charset='utf8'
    )
    return db

# 判断数据库是否存在
def create_database(db):
    cursor = db.cursor()
    cursor.execute('DROP DATABASE IF EXISTS test3;')
    init_db(db)

# 初始化数据库
def init_db(db):
    cursor = db.cursor()
    cursor.execute('CREATE DATABASE test3;')
    cursor.execute('USE test3;')
    cursor.execute("CREATE TABLE S (SNO VARCHAR(3) PRIMARY KEY, SNAME VARCHAR(5), STATUS INT , CITY VARCHAR(3), CHECK (STATUS=10 OR STATUS=20 OR STATUS=30 OR STATUS=40));")
    cursor.execute('CREATE TABLE P (PNO VARCHAR(3) PRIMARY KEY, PNAME VARCHAR(5), COLOR CHAR(1), WEIGHT INT);')
    cursor.execute('CREATE TABLE J (JNO VARCHAR(3) PRIMARY KEY, JNAME VARCHAR(5), CITY VARCHAR(3));')
    cursor.execute('CREATE TABLE SPJ (SNO VARCHAR(3), PNO VARCHAR(3), JNO VARCHAR(3), QTY INT, FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO));')
    cursor.execute("INSERT INTO S VALUES ('S1', '精益', 20, '天津'),('S2', '盛锡', 10, '北京'),('S3', '东方红', 30, '北京'),('S4', '丰泰盛', 20, '天津'),('S5', '为民', 30, '上海');")
    cursor.execute("INSERT INTO P VALUES ('P1', '螺母', '红', 12),('P2', '螺栓', '绿', 17),('P3', '螺丝刀', '蓝', 14),('P4', '螺丝刀', '红', 14),('P5', '凸轮', '蓝', 40),('P6', '齿轮', '红', 30);")
    cursor.execute("INSERT INTO J VALUES ('J1', '三建', '北京'),('J2', '一汽', '长春'),('J3', '弹簧厂', '天津'),('J4', '造船厂', '天津'),('J5', '机车厂', '唐山'),('J6', '无线电厂', '常州'),('J7', '半导体厂', '南京');")
    cursor.execute("INSERT INTO SPJ VALUES ('S1', 'P1', 'J1', 200),('S1', 'P1', 'J3', 100),('S1', 'P1', 'J4', 700),('S1', 'P2', 'J2', 100),('S2', 'P3', 'J1', 400),('S2', 'P3', 'J2', 200),('S2', 'P3', 'J4', 500),('S2', 'P3', 'J5', 400),('S2', 'P5', 'J1', 400),('S2', 'P5', 'J2', 100),('S3', 'P1', 'J1', 200),('S3', 'P3', 'J1', 200),('S4', 'P5', 'J1', 100),('S4', 'P6', 'J3', 300),('S4', 'P6', 'J4', 200),('S5', 'P2', 'J4', 100),('S5', 'P3', 'J1', 200),('S5', 'P6', 'J2', 200),('S5', 'P6', 'J4', 500);")
    db.commit()

# 主函数
def init_sql():
    db = connect()
    create_database(db)
    db.close()


if __name__ == '__main__':
    init_sql()
